package com.dosimple.comparedatabase.util;

import com.dosimple.comparedatabase.common.CommonResult;
import com.dosimple.comparedatabase.dto.TableDto;
import com.dosimple.comparedatabase.dto.TableColumnInfoDto;
import com.google.common.collect.Lists;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @auther: baolw
 * @date: 2018/6/19 0019 下午 5:07
 */
public class QueryUtils {

    public static CommonResult<List<String>> queryDBs(String baseId, String source, String sql) {
        CommonResult<List<String>> common = new CommonResult<>();
        List<String> result = new ArrayList<>();
        //1.加载驱动程序
        ResultSet rs = null;
        Statement st = null;
        Connection conn = null;
        try {
            conn = DBUtils.dbs.get(baseId).get(source).getConnection();
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            ResultSetMetaData metaData = rs.getMetaData();
            int count = metaData.getColumnCount();
            while (rs.next()) {
                for (int i = 0; i < count; i++) {
                    result.add(rs.getString(metaData.getColumnLabel(i + 1)));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            DBUtils.deleteDatabase(baseId);
            throw new RuntimeException(e.getMessage());
        } finally {
            //关闭资源
            DBUtils.closeConn(conn);
            DBUtils.closeStatement(st);
            DBUtils.closeResultSet(rs);
        }
        common.setData(result);
        return common;
    }

    public static CommonResult<List<String>> queryTables(String baseId, String source, String baseName) {
        CommonResult<List<String>> common = new CommonResult<>();
        List<String> result = new ArrayList<>();
        //1.加载驱动程序
        ResultSet rs = null;
        Statement st = null;
        Connection conn = null;
        try {
            //2.获得数据库链接
            conn = DBUtils.dbs.get(baseId).get(source).getConnection();
            rs = conn.getMetaData().getTables(baseName, "root", null, new String[]{"TABLE"});
            while (rs.next()) {
                result.add(rs.getString("TABLE_NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            DBUtils.deleteDatabase(baseId);
            throw new RuntimeException(e.getMessage());
        } finally {
            //关闭资源
            DBUtils.closeConn(conn);
            DBUtils.closeStatement(st);
            DBUtils.closeResultSet(rs);
        }
        common.setData(result);
        return common;
    }

    public static CommonResult<List<TableDto>> queryTableInfo(List<String> tableNames, String baseName, String source, String baseId) {
        CommonResult<List<TableDto>> common = new CommonResult<>();
        List<TableDto> result = new ArrayList<>();
        //1.加载驱动程序
        ResultSet rs = null;
        Statement st = null;
        Connection conn = null;
        try {
            //2.获得数据库链接
            conn = DBUtils.dbs.get(baseId).get(source).getConnection();
            DatabaseMetaData dmd = conn.getMetaData();
            for (String tableName: tableNames) {
                ResultSet colRet = dmd.getColumns(baseName,"root", tableName,"%");
                TableDto tableDto = new TableDto();
                tableDto.setTableName(tableName);
                List<TableColumnInfoDto> list = Lists.newArrayList();
                while(colRet.next()) {
                    TableColumnInfoDto info = new TableColumnInfoDto();
                    info.setColumnName(colRet.getString("COLUMN_NAME"));
                    info.setColumnType(colRet.getString("TYPE_NAME"));
                    info.setColumnSize(colRet.getInt("COLUMN_SIZE"));
                    info.setColumnDef(colRet.getString("REMARKS"));
                    info.setNullAble(colRet.getInt("NULLABLE") == 0 ? "否" : "是");
                    info.setDecimalDigits(colRet.getInt("DECIMAL_DIGITS"));
                    list.add(info);
                }
                tableDto.setInfoDtos(list);
                result.add(tableDto);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            DBUtils.deleteDatabase(baseId);
            throw new RuntimeException(e.getMessage());
        } finally {
            //关闭资源
            DBUtils.closeConn(conn);
            DBUtils.closeStatement(st);
            DBUtils.closeResultSet(rs);
        }
        common.setData(result);
        return common;
    }

}
